05. Set up a bookkeeping database

PRDTM2-787 AI Trading C4 L3 Vid5 Set Up A Bookkeeping Database

Setting Up a Bookkeeping Database for Trading

Using an SQLite3 database is crucial for tracking trading operations. The setup works like this:

Database Connection

  • Connect to SP500.db: Use this database file for local storage.

Structuring the Database

  • Function: Prepare: Collect relevant code for database preparation.

Positions Table

  • Purpose: Track position changes after each trade.
  • Columns:
    • Time of Trade: Text field for recording trade time.
    • Instrument: Name of the trading instrument, e.g., APEL.O for Apple or SPX 500 for S&P 500.
    • Quantity: Number of units held after trade. Positive for buy, negative for sell.
    • Cash: Available cash post-trade.

Table Features

  • Primary Key: Combination of Time of Trade and Instrument to prevent duplicate entries.
  • Efficiency: Primary key specification speeds up queries.

Stay tuned for coding the momentum strategy using geometric Brownian motion.

We have created the table stock_price in SQLite using the following statement
CREATE TABLE stock_price (
symbol text NOT NULL,
price real NOT NULL,
date text NOT NULL,
primary key (symbol, date)
);
What is the correct SQL statement to insert a new stock price for the symbol AAPL at a price of 150.25 on 2024-10-12?
SOLUTION: INSERT INTO stock_price (symbol, price, date) VALUES ('AAPL', 150.25, '2024-10-12');

Next Concept